package net.hn.hnms.biz.indicator.dao;

import net.hn.hnms.biz.indicator.entity.SysIndicator;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author 智呈物联 nzw
 * @date 2022/8/24
 */

@Repository
public interface SysIndicatorMapper extends JpaRepository<SysIndicator,Long> {

    List<SysIndicator> findAllByIndicatorFirstLevelCode(String firstCode);
    List<SysIndicator> findAllByIndicatorSecondLevelCode(String indicatorSecondLevelCode);

    @Query(value = "SELECT\n" +
            "\tss.* \n" +
            "FROM\n" +
            "\t( SELECT MAX ( indicator_third_level_code ) mx FROM ssys_indicator WHERE indicator_second_level_code = :secondCode ) ma,\n" +
            "\tssys_indicator ss \n" +
            "WHERE\n" +
            "\tCOALESCE(ma.mx,'') = COALESCE(ss.indicator_third_level_code,'') AND ss.indicator_second_level_code= :secondCode",nativeQuery = true)
    SysIndicator queryMaxThiredCode(@Param("secondCode")String secondCode);

    @Query(value = "SELECT\n" +
            "\tss.* \n" +
            "FROM\n" +
            "\t( SELECT MAX ( indicator_second_level_code ) mx FROM ssys_indicator WHERE indicator_first_level_code = :firstCode ) ma,\n" +
            "\tssys_indicator ss \n" +
            "WHERE\n" +
            "\tma.mx = ss.indicator_second_level_code and ss.indicator_first_level_code= :firstCode and \n" +
            "\tCOALESCE(indicator_third_level_code,'') = ''",nativeQuery = true)
    SysIndicator queryMaxSecondCode(@Param("firstCode")String firstCode);

    @Query(value = "SELECT MAX\n" +
            "\t( indicator_first_level_code ) \n" +
            "FROM\n" +
            "\tssys_indicator",nativeQuery = true)
    String queryMaxFirstCode();

    @Query(value = "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\tssys_indicator \n" +
            "WHERE\n" +
            "\tindicator_second_level_code = ? \n" +
            "\tAND indicator_third_level_code = ''",nativeQuery = true)
    SysIndicator querySecond(String secondCode);
}
